package net.searchdaily.java.util.excel;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

/**
 * Excel Util Example using Apache HSSF and XSSF by http://java.searchdaily.net.
 * 
 * @author namnvhue
 * 
 */
public class ExcelExporter {
	private static final int WIDTH = 325;
	List<TravelService> serviceList;

	public ExcelExporter() {
		createDummyData();
	}

	private void createDummyData() {
		serviceList = new ArrayList<TravelService>();
		serviceList.add(new TravelService("Air Fare", 100.0, 120.0, 130.0));
		serviceList.add(new TravelService("Hotel", 250.0, 350.0, 400.0));
		serviceList.add(new TravelService("Car Rental", 15.0, 15.0, 15.0));
		serviceList.add(new TravelService("Entertainment", 50.0, 100.0, 200.0));
	}

	public void exportExcelFile() {
		HSSFWorkbook workBook = new HSSFWorkbook();
		HSSFSheet sheet = workBook.createSheet("travelbook");
		this.exportHeader(workBook, sheet);
		int rowCount = 1;
		for (TravelService service : serviceList) {
			this.exportLine(service, rowCount, workBook, sheet);
			rowCount++;
		}

		try {
			OutputStream os = new BufferedOutputStream(new FileOutputStream(
					"C:\\travelbook.xls"));
			workBook.write(os);
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	private void exportLine(TravelService travelService, int rowNum,
			HSSFWorkbook wb, HSSFSheet sheet) {
		HSSFCellStyle style = null;
		HSSFRow row = sheet.createRow(rowNum);
		HSSFCell cell = null;

		// Service Name
		style = wb.createCellStyle();
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
		cell = row.createCell(0);
		cell.setCellStyle(style);
		cell.setCellValue(travelService.getName());

		// Standard Price
		style = wb.createCellStyle();
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		cell = row.createCell(1);
		cell.setCellStyle(style);
		cell.setCellValue(travelService.getStandardPrice());

		// Deluxe Price
		style = wb.createCellStyle();
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		cell = row.createCell(2);
		cell.setCellStyle(style);
		cell.setCellValue(travelService.getDeluxePrice());

		// Premium Price
		style = wb.createCellStyle();
		style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
		cell = row.createCell(3);
		cell.setCellStyle(style);
		cell.setCellValue(travelService.getPremiumPrice());

	}

	private void exportHeader(HSSFWorkbook wb, HSSFSheet sheet) {
		sheet.setColumnWidth(0, 15 * WIDTH);
		sheet.setColumnWidth(1, 20 * WIDTH);
		sheet.setColumnWidth(2, 15 * WIDTH);
		sheet.setColumnWidth(3, 35 * WIDTH);

		HSSFCellStyle style = wb.createCellStyle();
		HSSFFont font = wb.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		HSSFRow row = sheet.createRow(0);
		sheet.createFreezePane(0, 1, 0, 1);
		HSSFCell cell = row.createCell(0);
		// Service Name
		cell.setCellStyle(style);
		cell.setCellStyle(style);
		cell.setCellValue("Service Name");
		// Standard Package
		cell = row.createCell(1);
		cell.setCellStyle(style);
		cell.setCellValue("Standard Package");

		// Deluxe Package
		cell = row.createCell(2);
		cell.setCellStyle(style);
		cell.setCellValue("Deluxe Package");

		// Premium Package
		cell = row.createCell(3);
		cell.setCellStyle(style);
		cell.setCellValue("Premium Package");

	}
}
